using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.ApplicationBlocks.Data;

namespace Customs.Components.SLXNK
{
	public struct HPMDSearchInfo
	{
		public string MaDonVi;
		public string TenHang;
		public StringSearchWhat SWTenHang;
		public string MaLoaiHinh;
		public string MaHaiQuan;
		public DateTime TuNgay;
		public DateTime DenNgay;
		public decimal TGKB_VND;
		public byte TGKB_Compare;
	}
	
	/// <summary>
	/// Summary description for HangPhiMauDich.
	/// </summary>
	public class HangPhiMauDich
	{
		public static DataSet SearchByToKhaiKey(int soTK, string maLoaiHinh, string maHaiQuan, int namDK)
		{
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuan);
			return SearchByToKhaiKey(connectionString, soTK, maLoaiHinh, maHaiQuan, namDK);
		}

		public static DataSet SearchByToKhaiKey(string connStr, int soTK, string maLoaiHinh, string maHaiQuan, int namDK)
		{						
			StringBuilder query = new StringBuilder(string.Empty);
			query.Append("SELECT ");
			query.Append("* ");

			query.Append("FROM ");
			
			query.Append("DHANGPMD HPMD ");
			query.Append("LEFT JOIN SNUOC N ON HPMD.Nuoc_XX = N.Ma_Nuoc ");
			query.Append("LEFT JOIN SDVT DVT ON HPMD.Ma_DVT = DVT.Ma_DVT ");
			
			query.Append("WHERE ");
			
			query.Append("HPMD.SoTK = @SoToKhai AND ");
			query.Append("HPMD.MA_LH LIKE @MaLoaiHinh AND ");
			query.Append("HPMD.MA_HQ LIKE @MaHaiQuan AND ");
			query.Append("HPMD.NamDK = @NamDangKy ");
			
			query.Append("ORDER BY ");
			
			query.Append("HPMD.STTHang ");
			 
			SqlParameter[] parameters = new SqlParameter[4];
			parameters[0] = new SqlParameter("@SoToKhai", SqlDbType.Int);
			parameters[0].Value = soTK;

			parameters[1] = new SqlParameter("@MaLoaiHinh", SqlDbType.Char, 5);
			parameters[1].Value = maLoaiHinh;

			parameters[2] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
			parameters[2].Value = maHaiQuan;

			parameters[3] = new SqlParameter("@NamDangKy", SqlDbType.Int);
			parameters[3].Value = namDK;

			return SqlHelper.ExecuteDataset(connStr, CommandType.Text, query.ToString(), parameters);
		}
		//-----------------------------------------------------------------------------------------

		public static DataSet Search(HPMDSearchInfo hpmdSearchInfo)
		{
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", hpmdSearchInfo.MaHaiQuan);
			return Search(connectionString, hpmdSearchInfo);
		}
		
		public static DataSet Search(string connStr, HPMDSearchInfo hpmdSearchInfo)
		{
			StringBuilder query = new StringBuilder(string.Empty);
			query.Append("SELECT ");
			query.Append("(CONVERT(varchar(10), HPMD.SoTK) + '-' + HPMD.Ma_LH + '-' + RTRIM(HPMD.MA_HQ) + '-' + CONVERT(varchar(10), HPMD.NamDK) + '-' + CONVERT(varchar(10), HPMD.STTHang)) AS Khoa, ");
			query.Append("HPMD.*, ");
			query.Append("TK.Ngay_DK, ");
			query.Append("LH.Ten_LH, ");
			query.Append("HQ.Ma_HQ, ");
			query.Append("HQ.Ten_HQ, ");
			query.Append("N.Ten_Nuoc AS TenNuoc_XX, ");
			query.Append("DVT.Ten_DVT ");
			query.Append("FROM DHANGPMD HPMD ");
			query.Append("INNER JOIN SNUOC N ON HPMD.Nuoc_XX = N.Ma_Nuoc ");
			query.Append("INNER JOIN SDVT DVT ON HPMD.Ma_DVT = DVT.Ma_DVT ");			
			query.Append("INNER JOIN DTOKHAIPMD TK ON HPMD.SoTK = TK.SoTK AND HPMD.Ma_LH = TK.Ma_LH AND HPMD.Ma_HQ = TK.Ma_HQ AND HPMD.NamDK = TK.NamDK ");
			query.Append("INNER JOIN SHAIQUAN HQ ON TK.Ma_HQ = HQ.Ma_HQ ");
			query.Append("INNER JOIN SLHINHPMD LH ON TK.Ma_LH = LH.Ma_LH ");
			query.Append("WHERE ");
			// Ten hang.
			if (hpmdSearchInfo.TenHang.Trim().Length > 0)
			{
				string[] words;
				hpmdSearchInfo.TenHang = FontConverter.Unicode2TCVN(hpmdSearchInfo.TenHang);
				switch (hpmdSearchInfo.SWTenHang)
				{
					case StringSearchWhat.ALL_WORD:
						words = hpmdSearchInfo.TenHang.Split(' ');
						foreach (string word in words)
						{
							query.Append(string.Format("HPMD.Ten_Hang LIKE '%{0}%' AND ", word));
						}
						// Remove last AND in sql query.
						//query.Remove(query.Length - 5, 5);
						break;
					case StringSearchWhat.ANY_WORD:
						words = hpmdSearchInfo.TenHang.Split(' ');
						foreach (string word in words)
						{
							query.Append(string.Format("HPMD.Ten_Hang LIKE '%{0}%' OR ", word));
						}
						// Remove last OR in sql query.
						query.Remove(query.Length - 4, 4);
						query.Append(" AND ");
						break;
					case StringSearchWhat.EXACT:
						query.Append(string.Format("HPMD.Ten_Hang LIKE '%{0}%' AND ", hpmdSearchInfo.TenHang));
						break;
				}				
			}
			//			if (soToKhai > 0)
			//				query.Append(string.Format("SoTK = {0} AND ", soToKhai));
			//
			// Loai hinh.
			if (hpmdSearchInfo.MaLoaiHinh != "   ")
				query.Append(string.Format("TK.Ma_LH LIKE '{0}' AND ", hpmdSearchInfo.MaLoaiHinh));
			//
			// Hai quan mo to khai.
			if (hpmdSearchInfo.MaHaiQuan != "   ")
				query.Append(string.Format("TK.Ma_HQ LIKE '{0}' AND ", hpmdSearchInfo.MaHaiQuan));
			//
			//			// Nam dang ky.
			//			if (namDangKy > 1900)
			//				query.Append(string.Format("TK.NamDK = '{0}' AND ", namDangKy));
			//
			// Tu ngay -> den ngay.
			if (hpmdSearchInfo.TuNgay.Year > 1900 && hpmdSearchInfo.DenNgay.Year > 1900)
				query.Append("(TK.Ngay_DK >= @TuNgay AND TK.Ngay_DK <= @DenNgay) AND ");
			//
			//			// Ma don vi.
			//			if (maDonVi.Length > 0)
			//				query.Append(string.Format("TK.Ma_DV LIKE '{0}' AND ", maDonVi));
			//			
			//			// Tri gia khai bao.
			switch (hpmdSearchInfo.TGKB_Compare)
			{
				case 0:
					query.Append("HPMD.TGKB_VND >= @TGKB AND ");
					break;
				case 1:
					query.Append("HPMD.TGKB_VND <= @TGKB AND ");
					break;
				case 2:
					query.Append("HPMD.TGKB_VND = @TGKB AND ");
					break;
				default:
					query.Append("HPMD.TGKB_VND >= 0 AND ");
					break;
			}
			//				
			//			// Nuoc xuat khau.
			//			if (nuocXuatKhau != "XXX")
			//				query.Append(string.Format("TK.Nuoc_XK LIKE '{0}' AND ", nuocXuatKhau));
			//
			//			// Nuoc nhap khau.
			//			if (nuocXuatKhau != "XXX")
			//				query.Append(string.Format("TK.Nuoc_NK LIKE '{0}' AND ", nuocNhapKhau));
			//
			//			// Cua khau xuat, nhap.
			//			if (maCuaKhau != "XXX")
			//				query.Append(string.Format("TK.Ma_CK LIKE '{0}'", maCuaKhau));
			
			// Prevent error.
			query.Append("'1' = '1'");

			query.Append(" ORDER BY TK.Ngay_DK DESC, TK.SoTK DESC, TK.Ma_HQ");

			SqlParameter[] parameters = new SqlParameter[3];
			parameters[0] = new SqlParameter("@TuNgay", SqlDbType.DateTime);
			parameters[0].Value = hpmdSearchInfo.TuNgay;

			parameters[1] = new SqlParameter("@DenNgay", SqlDbType.DateTime);
			parameters[1].Value = hpmdSearchInfo.DenNgay;

			parameters[2] = new SqlParameter("@TGKB", SqlDbType.Decimal);
			parameters[2].Value = hpmdSearchInfo.TGKB_VND;

			try
			{				
				return SqlHelper.ExecuteDataset(connStr, CommandType.Text, query.ToString(), parameters);
			}
			catch (Exception ex)
			{
				throw new Exception(ex.Message + "----" + query.ToString());
			}
		}		
	}
}
